{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Musicians- Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ·········\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "**List the names, dates of birth and the instrument played of living musicians who play a instrument which Theo also plays.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "        <th>born</th>\n",
       "        <th>instrument</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>1951-02-28</td>\n",
       "        <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Harry Forte</td>\n",
       "        <td>1951-02-28</td>\n",
       "        <td>drums</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>James First</td>\n",
       "        <td>1965-06-10</td>\n",
       "        <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "        <td>1945-12-12</td>\n",
       "        <td>violin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>John Smith</td>\n",
       "        <td>1950-03-03</td>\n",
       "        <td>violin</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Harry Forte', datetime.date(1951, 2, 28), 'violin'),\n",
       " ('Harry Forte', datetime.date(1951, 2, 28), 'drums'),\n",
       " ('James First', datetime.date(1965, 6, 10), 'violin'),\n",
       " ('Jeff Dawn', datetime.date(1945, 12, 12), 'violin'),\n",
       " ('John Smith', datetime.date(1950, 3, 3), 'violin')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT m_name, born, instrument\n",
    "  FROM musician JOIN performer ON (musician.m_no=performer.perf_is)\n",
    "    WHERE instrument IN (\n",
    "        SELECT instrument FROM musician JOIN performer ON (musician.m_no=performer.perf_is) \n",
    "        WHERE m_name LIKE 'Theo%')\n",
    "    AND died IS NULL AND m_name NOT LIKE 'Theo%'\n",
    "    ORDER BY m_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "**List the name and the number of players for the band whose number of players is greater than the average number of players in each band.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>band_name</th>\n",
       "        <th>n_mbr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Oh well</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>AASO</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>ROP</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Oh well', 5), ('AASO', 7), ('ROP', 7)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT DISTINCT band_name, perf_is\n",
    "      FROM band JOIN plays_in ON (band.band_no=plays_in.band_id) JOIN\n",
    "        performer ON (performer.perf_no=plays_in.player)\n",
    ")\n",
    "SELECT band_name, COUNT(*) n_mbr\n",
    "  FROM t GROUP BY band_name\n",
    "    HAVING COUNT(*)>(SELECT COUNT(*) FROM t)/(SELECT COUNT(*) FROM band);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "**List the names of musicians who both conduct and compose and live in Britain.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>m_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Fred Bloggs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Jeff Dawn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Phil Hot</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Rose Spring</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Tony Smythe</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Fred Bloggs',),\n",
       " ('Jeff Dawn',),\n",
       " ('Phil Hot',),\n",
       " ('Rose Spring',),\n",
       " ('Tony Smythe',)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT DISTINCT m_name FROM\n",
    "  musician JOIN composer ON (musician.m_no=composer.comp_is) JOIN\n",
    "    place ON (musician.living_in=place.place_no) JOIN\n",
    "    performance ON (performance.conducted_by=musician.m_no) \n",
    "    WHERE place_country IN ('England', 'Scotland')\n",
    "    ORDER BY m_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "**Show the least commonly played instrument and the number of musicians who play it.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>n_player</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT instrument, COUNT(*)\n",
    "      FROM performer JOIN plays_in ON (performer.perf_no=plays_in.player) JOIN\n",
    "        performance ON (performance.gave=plays_in.band_id)\n",
    "        GROUP By instrument\n",
    "        ORDER BY COUNT(*)\n",
    "        LIMIT 1\n",
    ")\n",
    "SELECT COUNT(*) n_player\n",
    "  FROM performer JOIN t ON (performer.instrument=t.instrument);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "**List the bands that have played music composed by Sue Little; Give the titles of the composition in each case.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "5 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>band_name</th>\n",
       "        <th>c_title</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BBSO</td>\n",
       "        <td>Slow Symphony Blowing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>BBSO</td>\n",
       "        <td>Slow Song</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Somebody Loves this</td>\n",
       "        <td>Slow Symphony Blowing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Swinging strings</td>\n",
       "        <td>Slow Song</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>The left Overs</td>\n",
       "        <td>Slow Song</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('BBSO', 'Slow Symphony Blowing'),\n",
       " ('BBSO', 'Slow Song'),\n",
       " ('Somebody Loves this', 'Slow Symphony Blowing'),\n",
       " ('Swinging strings', 'Slow Song'),\n",
       " ('The left Overs', 'Slow Song')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "  SELECT c_no, c_title\n",
    "    FROM composition JOIN has_composed ON (composition.c_no=has_composed.cmpn_no) JOIN\n",
    "    composer ON (composer.comp_no=has_composed.cmpr_no) JOIN\n",
    "    musician ON (musician.m_no=composer.comp_is)\n",
    "    WHERE m_name='Sue Little'\n",
    ")\n",
    "SELECT band_name, c_title\n",
    "  FROM t JOIN performance ON (t.c_no=performance.performed) JOIN\n",
    "    band ON (performance.gave=band.band_no)\n",
    "    ORDER BY band_name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
